package com.demoweb.model.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;





import com.demoweb.model.dto.Score;

public class daesunScoreDao {
	public int insertscore(Score score1) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int scorenumber = -1;
		try {
			
			//2. 연결 생성
			conn = ConnectionHelper.getConnection();
//				"jdbc:mysql://127.0.0.1:3306/newdemoweb",	//url
//				"devuser", "mysql");	
			String sql = 
				"INSERT INTO score " + 
				"(memberid, score, gameno) " +
				"VALUES (?, ?, ?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, score1.getMemberId());
			pstmt.setInt(2, score1.getScore());
			pstmt.setInt(3, score1.getGameNo());
			pstmt.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { rs.close(); } catch (Exception ex) { }
			try { pstmt.close(); } catch (Exception ex) { }
			try { conn.close(); } catch (Exception ex) { }
		}
		return scorenumber;
	}
	
	
public List<Score> getscoreList(String id, int gameno) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Score> scores2 = new ArrayList<Score>();
		
		try {
//			//1. 드라이버 로딩
//			Class.forName("com.mysql.jdbc.Driver");
//			//2. 연결 생성
//			conn = DriverManager.getConnection(
//				"jdbc:mysql://172.16.3.1:3306/newdemoweb",	//url
//				"instructor", "mysql");						//account
			conn = ConnectionHelper.getConnection();
			//3. 명령 생성
			String sql = 
				"SELECT memberid, gameno, score, scoredate, scorenumber" + 
				"FROM score"+
				"where gameno = ?, memberid = ? ";
			pstmt = conn.prepareStatement(sql);		
			pstmt.setInt(1, gameno);
			pstmt.setString(2, id);
			//4. 명령 실행
			rs = pstmt.executeQuery();
			//5. 조회 결과를 처리 (SELECT QUERY인 경우)
			while (rs.next()) {
				Score score1= new Score();
				score1.setMemberId(rs.getString(1));
				score1.setGameNo(rs.getInt(2));
				score1.setScore(rs.getInt(3));
				score1.setScoreDate(rs.getTimestamp(4));
				score1.setScoreNumber(rs.getInt(5));
				
				scores2.add(score1);
			}
			
		} catch (Exception ex) {
			
		} finally {
			try { rs.close(); } catch (Exception ex) {}
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
		
		return scores2;
	}


public List<Score> getMembersScore2() {// 게시판 랭킹 
	
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	ArrayList<Score> scores2 = new ArrayList<Score>();
	
	try {
		
		
		conn = ConnectionHelper.getConnection();
		
		//3. 명령 생성
		String sql = 
		"SELECT memberid, score "+
		"FROM score "+
		"ORDER BY score DESC "; 
		pstmt = conn.prepareStatement(sql);						
		//4. 명령 실행
		rs = pstmt.executeQuery();
		//5. 조회 결과를 처리 (SELECT QUERY인 경우)
		while (rs.next()) {
			Score score = new Score();
			score.setMemberId(rs.getString(1));
			score.setScore(rs.getInt(2));
			scores2.add(score);
		}
	}	catch (Exception ex) {
		
	} finally {
		try { rs.close(); } catch (Exception ex) {}
		try { pstmt.close(); } catch (Exception ex) {}
		try { conn.close(); } catch (Exception ex) {}
	}
	
	return scores2;
}

public List<Score> getMyScore(String id, int gameno) {
	
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	ArrayList<Score> scores2 = new ArrayList<Score>();
	
	try {
//		//1. 드라이버 로딩
//		Class.forName("com.mysql.jdbc.Driver");
//		//2. 연결 생성
//		conn = DriverManager.getConnection(
//			"jdbc:mysql://172.16.3.1:3306/newdemoweb",	//url
//			"instructor", "mysql");						//account
		conn = ConnectionHelper.getConnection();
		//3. 명령 생성
		String sql = 
			"SELECT memberid, gameno, score, scoredate, scorenumber" + 
			"FROM score"+
			"where gameno = ?, memberid = ? ";
		pstmt = conn.prepareStatement(sql);		
		pstmt.setInt(1, gameno);
		pstmt.setString(2, id);
		//4. 명령 실행
		rs = pstmt.executeQuery();
		//5. 조회 결과를 처리 (SELECT QUERY인 경우)
		while (rs.next()) {
			Score score1= new Score();
			score1.setMemberId(rs.getString(1));
			score1.setGameNo(rs.getInt(2));
			score1.setScore(rs.getInt(3));
			score1.setScoreDate(rs.getTimestamp(4));
			score1.setScoreNumber(rs.getInt(5));
			
			scores2.add(score1);
		}
		
	} catch (Exception ex) {
		
	} finally {
		try { rs.close(); } catch (Exception ex) {}
		try { pstmt.close(); } catch (Exception ex) {}
		try { conn.close(); } catch (Exception ex) {}
	}
	
	return scores2;
}

}

